using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Acms.SqlDal
{
    public class SqlHelper
    {
 
        private static bool IsValueDBNull(object value)
        {
            bool isNull = false;
            if (value == null)
            {
                isNull = true;
            }
            else if (value is Guid && ((Guid)value) == Guid.Empty)
            {
                isNull = true;
            }
            else if (value is DateTime)
            {
                DateTime tmp = (DateTime)value;
                if (tmp == DateTime.MaxValue ||
                    tmp == DateTime.MinValue ||
                    tmp < new DateTime(1753, 1, 1, 12, 0, 0) ||
                    tmp > new DateTime(9999, 12, 31, 11, 59, 59)
                    )
                {
                    isNull = true;
                }
            }
            else if (value is string)
            {
                if ((string)value == "")
                {
                    isNull = true;
                }
            }
            return isNull;
        }

        private static object GetDBValue(object value)
        {
            object tmpValue = null;
            if (value != null)
            {
                //if an array convert to piped string list
                if (value is System.Xml.XmlDocument)
                {
                    System.Xml.XmlTextReader xmlReader = new System.Xml.XmlTextReader(new System.IO.StringReader(((System.Xml.XmlDocument)value).OuterXml));
                    tmpValue = new System.Data.SqlTypes.SqlXml(xmlReader);
                }
                else if (!(value is Byte[]) && value is Array)
                {
                    System.Text.StringBuilder tmp = new StringBuilder();
                    string spacer = "";
                    foreach (object s in (Array)value)
                    {
                        tmp.Append(spacer);
                        tmp.Append(s.ToString());
                        spacer = "|";
                    }
                    tmpValue = tmp.ToString();
                }
                else if (value is DateTime)
                {
                    tmpValue = ((DateTime)value).ToUniversalTime();
                }
                else if (value is System.Net.IPAddress)
                {
                    tmpValue = value.ToString();
                }
                else
                {
                    tmpValue = value;
                }
            }
            return tmpValue;

        }

        public static void AddInputParameter(SqlCommand cmd, string name, object value, SqlDbType type, int? size, byte? pression, byte? scale)
        {
            AddParameter(cmd, name, type, size, scale, pression);
            cmd.Parameters[cmd.Parameters.Count - 1].Direction = ParameterDirection.Input;

            if (IsValueDBNull(value))
            {
                cmd.Parameters[cmd.Parameters.Count - 1].Value = DBNull.Value;
            }
            else
            {
                cmd.Parameters[cmd.Parameters.Count - 1].Value = GetDBValue(value);
            }
        }

        public static void AddOutputParameter(SqlCommand cmd, string name, SqlDbType type, int? size, byte? scale, byte? pression)
        {
            AddParameter(cmd, name, type, size, scale, pression);
            cmd.Parameters[cmd.Parameters.Count - 1].Direction = ParameterDirection.Output;
        }

        public static void AddReturnParameter(SqlCommand cmd, SqlDbType type, int? size, byte? scale, byte? pression)
        {
            AddParameter(cmd, "@RetVal", type, size, scale, pression);
            cmd.Parameters[cmd.Parameters.Count - 1].Direction = ParameterDirection.ReturnValue;
        }

        private static void AddParameter(SqlCommand cmd, string name, SqlDbType type, int? size, byte? scale, byte? pression)
        {
            SqlParameter tmp = new SqlParameter();
            tmp.ParameterName = name;
            tmp.SqlDbType = type;

            if (size != null)
            {
                tmp.Size = size.Value;
            }

            if (scale != null)
            {
                tmp.Scale = scale.Value;
            }

            if (pression != null)
            {
                tmp.Precision = pression.Value;
            }

            cmd.Parameters.Add(tmp);
        }

        private static T GetValue<T>(object val)
        {
            T retVal;
            if (val == DBNull.Value)
            {
                retVal = default(T);
            }
            else if (val is DateTime)
            {
                object local = DateTime.SpecifyKind((DateTime)val, DateTimeKind.Utc).ToLocalTime();
                return (T)local;
            }
            else if (typeof(T) == typeof(System.Net.IPAddress))
            {
                retVal = (T)(object)System.Net.IPAddress.Parse(val as string);
            }
            else
            {
                retVal = (T)val;
            }
            return retVal;

        }

        private static T GetParamValue<T>(SqlParameter p)
        {
            T ret = default(T);
            if (p != null)
            {
                ret = GetValue<T>(p.Value);
            }
            return ret;
        }
        
        public static T GetOutputValue<T>(SqlCommand command, string name)
        {
            T ret = default(T);
            if (command.Parameters[name] != null)
            {
                try
                {
                    ret = GetParamValue<T>(command.Parameters[name]);
                }
                catch (InvalidCastException)
                {
                    throw new Exceptions.LoadDataException(string.Format("Output Parameter '@{0}' From Command '{1}' cound not be cast to an '{2}'. Value returned '{3}' as '{4}'", name, command.CommandText, typeof(T).ToString(), command.Parameters[string.Format("@{0}", name)].Value, command.Parameters[string.Format("@{0}", name)].SqlDbType));
                }
            }
            return ret;
        }

        public static T GetReturnValue<T>(SqlCommand command)
        {
            T ret = default(T);
            if (command.Parameters["@RetVal"] != null)
            {
                ret = GetParamValue<T>(command.Parameters["@RetVal"]);
            }
            return ret;
        }

        public static SqlDataReader ExecuteDataReaderCommand(SqlCommand command, SqlConnection connection, CommandBehavior behavior)
        {
            try
            {
                if(command.Transaction == null)
                {
                    command.Connection = connection;
                    command.Connection.Open();
                }
                return command.ExecuteReader(behavior);
            }
            catch
            {
                if (command.Transaction != null)
                {
                    command.Transaction.Rollback();
                }
                if (command.Connection != null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                connection.Dispose();
                connection = null;
                throw;
            }
        }

        public static void ExecuteNonQueryCommand(SqlCommand command, SqlConnection connection)
        {
            try
            {
                if(command.Transaction == null)
                {
                    command.Connection = connection;
                    command.Connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch
            {
                if (command.Transaction != null)
                {
                    command.Transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (command.Transaction == null && command.Connection != null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }
        }

        public static T GetReaderValue<T>(string name, SqlDataReader reader)
        {
            int i = -1;
            try
            {
                i = reader.GetOrdinal(name);
            }
            catch (IndexOutOfRangeException)
            {
                throw new Exceptions.LoadDataException(string.Format("Field '{0}' could not be found.", name));
            } 

            object val;
            T tmp;

            val = reader.GetValue(i);
            try
            {
                tmp = GetValue<T>(val);
            }
            catch (InvalidCastException)
            {
                throw new Exceptions.LoadDataException(string.Format("Field '{0}' could not be cast to expected type '{1}'. Value Type = '{2}'. Value = '{3}'", name, typeof(T).ToString(), val.GetType().ToString(), val.ToString()));
            }
            if (tmp != null)
            {
                return tmp;
            }
            else if (val == DBNull.Value && (tmp == null || tmp is Guid || tmp is DateTime))
            {
                return tmp;
            }
            else
            {
                throw new Exceptions.LoadDataException(string.Format("Field '{0}' is DBNull but Value Type = '{1}' can not contain null", name, typeof(T).ToString()));
            }
        }
    }
}


